﻿

using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using RBS.Core.Extension;
using RBS.DBUtility;
using RBS.Common;

//Please add references
namespace RBS.Core.DAL.Hotel
{
	/// <summary>
	/// 数据访问类:H_Hotels
	/// </summary>
	public partial class H_Hotels
	{
		public H_Hotels()
		{}
        #region  BasicMethod
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int hId)
        {
            var strSql = new StringBuilder();
            strSql.Append("select count(1) from H_Hotels");
            strSql.Append(" where H_id=@H_id");
            SqlParameter[] parameters = {
					new SqlParameter("@H_id", SqlDbType.Int,4)
			};
            parameters[0].Value = hId;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(RBS.Core.Model.Hotel.H_Hotels model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into H_Hotels(");
            strSql.Append("Parking,WIFI,Cutlery,Feature,Promotional,AuditStatus,License,City,Province,Region,H_Name,IsRecommend,Address,Telphone,Mobile,ShopBegintime,ShopEndtime,Cuisine,Location");
            strSql.Append(") values (");
            strSql.Append("@Parking,@WIFI,@Cutlery,@Feature,@Promotional,@AuditStatus,@License,@City,@Province,@Region,@H_Name,@IsRecommend,@Address,@Telphone,@Mobile,@ShopBegintime,@ShopEndtime,@Cuisine,@Location");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@Parking", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@WIFI", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Cutlery", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Feature", SqlDbType.NVarChar,500) ,            
                        new SqlParameter("@Promotional", SqlDbType.NVarChar,500) ,            
                        new SqlParameter("@AuditStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@License", SqlDbType.NVarChar,200) ,            
                        new SqlParameter("@City", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Province", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Region", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@H_Name", SqlDbType.NVarChar,200) ,            
                        new SqlParameter("@IsRecommend", SqlDbType.Int,4) ,            
                        new SqlParameter("@Address", SqlDbType.NVarChar,1000) ,            
                        new SqlParameter("@Telphone", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Mobile", SqlDbType.NVarChar,11) ,            
                        new SqlParameter("@ShopBegintime", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@ShopEndtime", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Cuisine", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Location", SqlDbType.NVarChar,2000)             
              
            };

            parameters[0].Value = model.Parking;
            parameters[1].Value = model.WIFI;
            parameters[2].Value = model.Cutlery;
            parameters[3].Value = model.Feature;
            parameters[4].Value = model.Promotional;
            parameters[5].Value = model.AuditStatus;
            parameters[6].Value = model.License;
            parameters[7].Value = model.City;
            parameters[8].Value = model.Province;
            parameters[9].Value = model.Region;
            parameters[10].Value = model.H_Name;
            parameters[11].Value = model.IsRecommend;
            parameters[12].Value = model.Address;
            parameters[13].Value = model.Telphone;
            parameters[14].Value = model.Mobile;
            parameters[15].Value = model.ShopBegintime;
            parameters[16].Value = model.ShopEndtime;
            parameters[17].Value = model.Cuisine;
            parameters[18].Value = model.Location;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {

                return Convert.ToInt32(obj);

            }

        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(RBS.Core.Model.Hotel.H_Hotels model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update H_Hotels set ");

            strSql.Append(" Parking = @Parking , ");
            strSql.Append(" WIFI = @WIFI , ");
            strSql.Append(" Cutlery = @Cutlery , ");
            strSql.Append(" Feature = @Feature , ");
            strSql.Append(" Promotional = @Promotional , ");
            strSql.Append(" AuditStatus = @AuditStatus , ");
            strSql.Append(" License = @License , ");
            strSql.Append(" City = @City , ");
            strSql.Append(" Province = @Province , ");
            strSql.Append(" Region = @Region , ");
            strSql.Append(" H_Name = @H_Name , ");
            strSql.Append(" IsRecommend = @IsRecommend , ");
            strSql.Append(" Address = @Address , ");
            strSql.Append(" Telphone = @Telphone , ");
            strSql.Append(" Mobile = @Mobile , ");
            strSql.Append(" ShopBegintime = @ShopBegintime , ");
            strSql.Append(" ShopEndtime = @ShopEndtime , ");
            strSql.Append(" Cuisine = @Cuisine , ");
            strSql.Append(" Location = @Location  ");
            strSql.Append(" where H_id=@H_id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@H_id", SqlDbType.Int,4) ,            
                        new SqlParameter("@Parking", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@WIFI", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Cutlery", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Feature", SqlDbType.NVarChar,500) ,            
                        new SqlParameter("@Promotional", SqlDbType.NVarChar,500) ,            
                        new SqlParameter("@AuditStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@License", SqlDbType.NVarChar,200) ,            
                        new SqlParameter("@City", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Province", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Region", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@H_Name", SqlDbType.NVarChar,200) ,            
                        new SqlParameter("@IsRecommend", SqlDbType.Int,4) ,            
                        new SqlParameter("@Address", SqlDbType.NVarChar,1000) ,            
                        new SqlParameter("@Telphone", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Mobile", SqlDbType.NVarChar,11) ,            
                        new SqlParameter("@ShopBegintime", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@ShopEndtime", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Cuisine", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Location", SqlDbType.NVarChar,2000)             
              
            };

            parameters[0].Value = model.H_id;
            parameters[1].Value = model.Parking;
            parameters[2].Value = model.WIFI;
            parameters[3].Value = model.Cutlery;
            parameters[4].Value = model.Feature;
            parameters[5].Value = model.Promotional;
            parameters[6].Value = model.AuditStatus;
            parameters[7].Value = model.License;
            parameters[8].Value = model.City;
            parameters[9].Value = model.Province;
            parameters[10].Value = model.Region;
            parameters[11].Value = model.H_Name;
            parameters[12].Value = model.IsRecommend;
            parameters[13].Value = model.Address;
            parameters[14].Value = model.Telphone;
            parameters[15].Value = model.Mobile;
            parameters[16].Value = model.ShopBegintime;
            parameters[17].Value = model.ShopEndtime;
            parameters[18].Value = model.Cuisine;
            parameters[19].Value = model.Location;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
		
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int hId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from H_Hotels ");
            strSql.Append(" where H_id=@H_id");
            SqlParameter[] parameters = {
					new SqlParameter("@H_id", SqlDbType.Int,4)
			};
            parameters[0].Value = hId;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string hIdlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from H_Hotels ");
            strSql.Append(" where H_id in (" + hIdlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

         

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public RBS.Core.Model.Hotel.H_Hotels GetModel(int H_id)
        { 
            var strSql = new StringBuilder();
            strSql.Append("select H_id, Parking, WIFI, Cutlery, Feature, Promotional, AuditStatus, License, City, Province, Region, H_Name, IsRecommend, Address, Telphone, Mobile, ShopBegintime, ShopEndtime, Cuisine, Location  ");
            strSql.Append("  from H_Hotels ");
            strSql.Append(" where H_id=@H_id");
            SqlParameter[] parameters = {
					new SqlParameter("@H_id", SqlDbType.Int,4)
			};
            parameters[0].Value = H_id; 

            var model = new RBS.Core.Model.Hotel.H_Hotels();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters); 
            if (ds.Tables[0].Rows.Count > 0)
            {
                model = ds.Tables[0].Rows[0].ConvertToEntity<RBS.Core.Model.Hotel.H_Hotels>();
                return model;
            }
            else
            {
                return null;
            }
        }
          
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public RBS.Core.Model.Hotel.H_Hotels DataRowToModel(DataRow row)
        {
            var model = new Model.Hotel.H_Hotels();
            var entity = row.ConvertToEntity<RBS.Core.Model.Hotel.H_Hotels>(); 
            return entity;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select H_id, Parking, WIFI, Cutlery, Feature, Promotional, AuditStatus, License, City, Province, Region, H_Name, IsRecommend, Address, Telphone, Mobile, ShopBegintime, ShopEndtime, Cuisine, Location  ");
            strSql.Append(" FROM H_Hotels ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append("  H_id, Parking, WIFI, Cutlery, Feature, Promotional, AuditStatus, License, City, Province, Region, H_Name, IsRecommend, Address, Telphone, Mobile, ShopBegintime, ShopEndtime, Cuisine, Location   ");
            strSql.Append(" FROM H_Hotels ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetListRecommend(int Top, string strWhere, string filedOrder)
	    {
	        StringBuilder strSql = new StringBuilder();
	        strSql.Append("select ");
	        if (Top > 0)
	        {
	            strSql.Append(" top " + Top.ToString());
	        }
	        strSql.Append(
                " Id, Name, Address, Telphone, City, Region, Categories, ImgId, Location, IsRecommend, Province, lng, lat, Renjun, Pingfen, Img, HotHotel ");
            strSql.Append(" FROM V_Hotel ");
	        if (strWhere.Trim() != "")
	        {
	            strSql.Append(" where " + strWhere);
	        }
	        strSql.Append(" order by " + filedOrder + " desc");
	        return DbHelperSQL.Query(strSql.ToString());
	    }

	    /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM H_Hotels ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.H_id desc");
            }
            strSql.Append(")AS Row, T.*  from H_Hotels T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE 1=1" + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion
        #region  ExtensionMethod
        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM H_Hotels");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where 1=1 " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }

        public DataSet GetList(decimal clng, decimal clat, int km, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * FROM fnGetHotelLoction(" + clng + "," + clat + "," + km + ")");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where 1=1 " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
		#endregion  ExtensionMethod

        public bool HotelAudit(int hid,int auditStatus)
        {
            const string strSql = "update H_Hotels set AuditStatus=@AuditStatus where H_id=@H_id";
            SqlParameter[] parameters = { 
                    new SqlParameter("@AuditStatus", SqlDbType.Int,4),
					new SqlParameter("@H_id", SqlDbType.Int,4)};
            parameters[0].Value = auditStatus;
            parameters[1].Value = hid;
            int rows = DbHelperSQL.ExecuteSql(strSql, parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public int GetRenJun(int hid)
        {
            string strSql =
                "select isnull(sum(orderamount)/sum(dinenum),0) as renjun from d_order  where h_id=@hid and orderstate in (5,6,7)";
            SqlParameter[] parameters = {  
					new SqlParameter("@hid", SqlDbType.Int,4)};
            parameters[0].Value = hid;
            var rj =  DbHelperSQL.Query(strSql, parameters).Tables[0].Rows[0][0]; 
            return Convert.ToInt32(rj);
        }
    }
}

